﻿
--获取公司员工档案,统计员工档案new
CREATE PROCEDURE [dbo].[proc_Employee_GetCompanyList_Excel_FY_new]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@ygxm nvarchar(max),--员工姓名，多姓名查询，用逗号分隔
	@dw varchar(8000),--公司  --公司ID
	@bm varchar(8000)--部门 --部门名称
)	
as

DECLARE @sqltj VARCHAR(MAX)--查询条件
set @sqltj=''

if(@ygxm<>'')
	set @sqltj = @sqltj + ' AND EName in (' + @ygxm + ') '

if(@bm <> '' and @bm <>'0')
	set @sqltj = @sqltj + ' AND departmentid in (' + @bm + ') '

if(@dw<>'' and @dw<>'0')
	set @sqltj = @sqltj + ' AND CompanyId IN ('+ @dw + ') '

if (@sqltj = '')
	return

--if(@bm <>'' or @bm<>'0' or @bm <> null)
--  if @ygxm<>''
--     set @sqltj=@sqltj+' and departmentid in (Select Id from Department where DName='''+@bm+''')'
--  else
-- 	set @sqltj=@sqltj+' where departmentid in (Select Id from Department where DName='''+@bm+''') and State!= 5'

	declare @sql3 nvarchar (4000)
	begin
	set @sql3 = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable (nid)'
		+ ' select id '
		+ ' from employee'
				+ ' where State!= 5 ' + @sqltj
		+' order by enumber asc'

		+' select' 
		+'  E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.RuzhiDate'--入职年限
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'

		+' , Case When (select ParentId from Department where Id=E.DepartmentId)=0  then  (select DName from Department where Id=E.DepartmentId)
                  When (Select ParentId from Department where Id=(select ParentId from Department where Id=E.DepartmentId))=0  then ((select Dname from Department where Id=(select ParentId from Department where Id=E.DepartmentId)) +''/''+( (select DName from Department where Id=E.DepartmentId)))
                   Else		               
					  (select DName from Department where Id=(select ParentId from Department where Id=(select ParentId from Department where Id=E.DepartmentId))) 
					  +''/''+ (select DName from Department where Id=E.DepartmentId)
					  +''/''+ (select Dname from Department where Id=(select ParentId from Department where Id=E.DepartmentId))
            End  ParentDepartmentName'--部门


		+' ,E.ZzDate'
		+' ,E.PostId'
		+' ,(select PostName from Post where id=E.PostId) as PostName'
		+' ,E.GradeId'
		+' ,(select Title from Grade where id=E.GradeId) as GradeName'--职级
 		+' ,(select count(0) from @indextable) as coun'
 		+' ,E.Birthday'
 		+' ,E.Marriage'
 		+' ,E.PoliticsStatus'
 		+' ,(select Title from Employee_Education where id=E.Education) as Education'--学历
 		+' ,(select top(1) School from Employee_EducationResume where EId=E.id order by EndDate desc) as Employee_EducationResume'--毕业学校
 		+' ,E.IDcard'
 		+' ,E.MobilePhone'
 		+' ,E.HomePhone'
 		+' ,E.Email'
 		+' ,E.Address'
 		+' ,E.WageCardNo'
 		+' ,E.Cuenta'
		+' ,E.ZhiWu' --职务
		+' ,E.XZJB' --行政级别
		+' ,(Select MAX(ISNULL(Execdate,''1900-01-01'')) from
			  (
				select	MAX(ISNULL(Execdate,''1900-01-01'')) AS Execdate from Employee_CompanyMove where eid= E.id and  ACompanyId is NOT NULL AND Audit = 1	--单位异动
				union
				select MAX(ISNULL(Execdate,''1900-01-01'')) AS Execdate from Employee_PostMove where eid= E.id and secondAudit=1 --岗位异动
			  ) as Execdate
		    ) as NewPromotionDate'--上次晋级时间
			
		--以下是所有薪资，相加即为薪资总和
 		+' ,(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage'
 		+' ,(isnull((select p.PostWage from Post p where p.id=E.PostId),0)) as PostWage'
 		+' ,(datediff(mm,ISNULL(e.RuzhiDate,GetDate()),GetDate())/12*100) as WorkWage'
 		+' ,(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage'
 		+' ,(isnull((select ManQin from Post where id=E.PostId),0)) as ManQin'
 		+' ,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = E.UserName),0) as FAllowance'
 		+' ,ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where PostId=E.PostId and EA.Pid=E.Pid and EA.Cid=E.Cid),0) as Allowance'
 				
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where t.id between '+ @startIndex +' and '+ @endIndex+' order by CompanyId'
		
	print (@sql3)
	exec (@sql3)
	END
	RETURN	



